using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static ParametroVO getParametro(int? idParametro, string parametro)
		{
			ParametroVO parametroVO = null;
			List<ParametroVO> listParametroVOs = getParametros(idParametro, parametro);
			if (listParametroVOs != null && listParametroVOs.Count > 0)
			{
				parametroVO = listParametroVOs[0];
			}
			return parametroVO;
		}

		public static ParametroVO getParametro(int? idParametro)
		{
			return getParametro(idParametro, null);
		}

		public static List<ParametroVO> getParametros()
		{
			return getParametros(null, null);
		}

		public static List<ParametroVO> getParametros(int? idParametro)
		{
			return getParametros(idParametro, null);
		}

		public static List<ParametroVO> getParametros(int? idParametro, string parametro)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT par.idParametro as par_idParametro, par.parametro as par_parametro, par.valor as par_valor, par.Cadastro as par_Cadastro, par.CadastradoPor as par_CadastradoPor, par.Edicao as par_Edicao, par.EditadoPor as par_EditadoPor  ");
			query.AppendLine("FROM parametro as par ");
			if (idParametro != null)
			{
				query.AppendLine(where + "par.idParametro=@idParametro ");
				where = "AND ";
			}
			if (parametro != null)
			{
				query.AppendLine(where + "par.parametro LIKE @parametro ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY par.parametro ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idParametro!= null) cmd.Parameters.Add(getParameter(cmd, "@idParametro", idParametro));
			if (parametro!= null) cmd.Parameters.Add(getParameter(cmd, "@parametro", "%"+parametro+"%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ParametroVO> listParametro = new List<ParametroVO>();

				while (reader.Read())
				{
					ParametroVO parametroVO = ParametroVO.CreateVO();
					parametroVO.idParametro = reader.GetInt32(reader.GetOrdinal("par_idParametro"));
					parametroVO.parametro = reader.GetString(reader.GetOrdinal("par_parametro"));
					parametroVO.valor = reader.GetString(reader.GetOrdinal("par_valor"));
					parametroVO.cadastro = reader.GetDateTime(reader.GetOrdinal("par_Cadastro"));
					parametroVO.cadastradoPor = reader.GetString(reader.GetOrdinal("par_CadastradoPor"));
					parametroVO.edicao = reader.GetDateTime(reader.GetOrdinal("par_Edicao"));
					parametroVO.editadoPor = reader.GetString(reader.GetOrdinal("par_EditadoPor"));


					listParametro.Add(parametroVO);
				}
				reader.Close();
				return listParametro;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setParametro(ParametroVO parametroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE parametro ");
			query.AppendLine("SET parametro=@parametro, valor=@valor,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idParametro=@idParametro ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idParametro", parametroVO.idParametro));
			cmd.Parameters.Add(getParameter(cmd, "@parametro", parametroVO.parametro));
			cmd.Parameters.Add(getParameter(cmd, "@valor", parametroVO.valor));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", parametroVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", parametroVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", parametroVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", parametroVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newParametro(ParametroVO parametroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO parametro ");
			query.AppendLine("  ( parametro, valor, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @parametro, @valor, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@parametro", parametroVO.parametro));
			cmd.Parameters.Add(getParameter(cmd, "@valor", parametroVO.valor));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", parametroVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", parametroVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", parametroVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", parametroVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('parametro') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delParametro(ParametroVO parametroVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM parametro ");
			query.AppendLine("WHERE idParametro=@idParametro ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idParametro", parametroVO.idParametro));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
	}
}
